from sqlalchemy.dialects.mysql import MEDIUMTEXT
from sqlalchemy.orm import scoped_session, sessionmaker, declarative_base
from sqlalchemy import create_engine, Column, Integer, String, UniqueConstraint, Text, exc, DateTime, inspect, ARRAY, \
    or_, and_, JSON, DATETIME

# 创建基类
Base = declarative_base()
# 定义模型
class Project_Table(Base):
    __tablename__ = 'home_new'
    home_id = Column(Integer, primary_key=True, autoincrement=True, comment='自增主键')  # 自增主键
    issuer_full_name = Column(String(255), nullable=False, comment='发行人全称')
    detail_page_url = Column(String(255), nullable=False, comment='详情页_链接')
    sector = Column(String(255), nullable=False, comment='板块')
    audit_status = Column(String(255), nullable=False, comment='审核状态')
    registered_address = Column(String(255), nullable=False, comment='注册地')
    industry = Column(String(255), nullable=False, comment='行业分类')
    recommendation_institution = Column(String(255), nullable=False, comment='保荐机构')
    recommendation_institution_url = Column(String(255), comment='保荐机构_链接')
    law_firm = Column(String(255), nullable=False, comment='律师事务所')
    law_firm_url = Column(String(255), comment='律师事务所_链接')
    accounting_firm = Column(String(255), nullable=False, comment='会计师事务所')
    accounting_firm_url = Column(String(255), comment='会计师事务所_链接')
    update_date = Column(DATETIME, nullable=False, comment='更新日期')
    accept_date = Column(DATETIME, nullable=False, comment='受理日期')
    stock_exchange = Column(String(255), nullable=False, comment='证券交易所')

    __table_args__ = (
        UniqueConstraint('issuer_full_name', 'audit_status', 'stock_exchange', name='唯一值'),
    )

# 创建数据库引擎和会话
DATABASE_URI = 'mysql+pymysql://root:grow1234@nj-cdb-1mwvwkzt.sql.tencentcdb.com:63972/stock_exchange'
POOL_SIZE = 200
MAX_OVERFLOW = 300
engine = create_engine(DATABASE_URI, pool_size=POOL_SIZE, max_overflow=MAX_OVERFLOW)

# 检查表格是否已存在
inspector = inspect(engine)
if not inspector.has_table("home_new"):
    # 表格不存在，创建表格
    Base.metadata.create_all(engine)
    print('表格已成功创建')
else:
    # 表格已存在
    print('表格已存在，无需创建')

# 假设engine是预先创建好的数据库引擎
DBSession = scoped_session(sessionmaker(bind=engine))